package mwa.cs.dao;

import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.Reader;

/**
 * A class used for managing route
 */
public class ReaderDAO {

	/** a database of this reader */
    private Database db;
    
    /**
     * Instantiate a new object of this class
     * @param database a database
     */
    public ReaderDAO(Database db) {
        this.db = db;
    }
    
    /**
     * a method for searching all route in database
     * @return all reader
     */
    public List<Map<String, Object>> findAll() {
        String vaSql = "SELECT * FROM reader";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    /**
     * a method for searching reader by site in database
     * @return reader where are in the site
     */
    public List<Map<String, Object>> findReaderBySite(int pnSiteId) {
        String vaSql = "SELECT site_id, reader_id, reader_name FROM reader WHERE site_id = ? AND status = 1 ORDER BY reader_id ASC";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    //get reader by site id
    public List<Map<String, Object>> findsReaderBySite(int pnSiteId) {
        String vaSql = "SELECT site_id, reader_id, reader_name ,status FROM reader WHERE site_id = ? ORDER BY reader_id ASC";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    //get reader_name by site_id AND reader_id
    public String findReaderNameBySiteAndReaderId(int pnSiteId, String paReaderId,String paField) {
        String vaSql = "SELECT "+ paField +" FROM reader WHERE site_id = ? and reader_id = ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paReaderId);
        if (result != null) {
            return (String) result.get(paField);
        } else {
            return null;
        }
    }
    
    //get reader_name by site_id 
   /* public String findReaderNameBySite(int pnSiteId,int pnReaderId,String field) {
        String vaSql = "SELECT "+ field +" FROM reader WHERE site_id = ? AND reader_id = ? AND status = '1'";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId,pnReaderId);
        if (result != null) {
            return (String) result.get(field);
        } else {
            return null;
        }
    }*/
    
    
    
    //get reader_id for check duplicate for insert data
    public String findReaderIdBySiteAndReader(int pnSiteId, int paReaderId) {
        String vaSql = "SELECT reader_id  FROM  reader WHERE site_id = ? and reader_id = ? ";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paReaderId);
        if (result != null) {
            return (String) result.get("READER_ID").toString();
        } else {
            return null;
        }
    }
    
    //get reader_id for check duplicate for update data
    public String findsReaderIdBySiteAndReader(int pnSiteId, int paOldReaderId,int paNewReaderId) {
        String vaSql = "SELECT reader_id FROM reader WHERE site_id = ? and reader_id != ? and reader_id = ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId,paOldReaderId,paNewReaderId);
        if (result != null) {
            return (String) result.get("READER_ID").toString();
        } else {
            return null;
        }
    }
    
    //get Reder_name and reader_id 
    public List<Map<String, Object>> findReaderNameAndReaderIdBySiteAndReaderId() {
        String vaSql = "select  reader_id , reader_name  from reader ORDER BY 'ASC'";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    
    //insert reader
    public void addReader(Reader poReader) {
   	 String vaSql = "INSERT INTO READER (SITE_ID,READER_ID,READER_NAME,STATUS,INSERT_USER_ID,INSERT_DATE,INSERT_TIME,"
   	 								+ "UPDATE_USER_ID,UPDATE_DATE,UPDATE_TIME)"
   			 						+"VALUES(?,?,?,?,?,?,?,?,?,?)";
   			try {
   					db.insert(vaSql, poReader.getVnSiteId(),
   							poReader.getVnReaderId(),
   							poReader.getVaReaderName(),
   							poReader.getVaStatus(),
   							poReader.getVaInsertUserId(),
   							poReader.getVaInsertDate(), 
   							poReader.getVaInsertTime(),
   							poReader.getVnUpdateUserId(),
   							poReader.getvaUpdateDate(),
   							poReader.getVaUpdateTime());
   				
   			}catch (Exception e) {
   	        	throw new RuntimeException(e);
   	        }
   	}
    
    //update reader  
    public void updateReader(int pnSiteId,int pnReaderId,Reader poReader) {
	 	 
    	String vaSql  = " UPDATE READER SET " +
    				  " READER_ID = ? , " +
    				  " READER_NAME  = ? , " +
    				  " STATUS = ? , " +
    				  " UPDATE_USER_ID = ? ,"+
    				  " UPDATE_DATE = ? ," +
    				  " UPDATE_TIME = ? " +
    				  " WHERE " + 
    				  " READER_ID = ? " +
    				  " AND SITE_ID = ? ";
    	
    	 try {
				db.update(vaSql,
							poReader.getVnReaderId(),
							poReader.getVaReaderName(),
							poReader.getVaStatus(),
							poReader.getVnUpdateUserId(),
							poReader.getvaUpdateDate(),
							poReader.getVaUpdateTime(),
							pnReaderId,
							pnSiteId);
							
		}catch (Exception e) {
			throw new RuntimeException(e);
		}
    }
    
    //delete reader
    public boolean deleteReaderBySiteId(int pnSiteId,int pnReaderId) {
        String vaSql = "DELETE FROM READER WHERE SITE_ID = ? and READER_ID = ?";
        try {
            db.update(vaSql, pnSiteId,pnReaderId);
            return true;
        } catch (Exception e) {
            return false;
        }
    }
    
    
}
